Common Table Expressions

A Common Table Expression (CTE) is a temporary result set defined within the execution of a single SQL statement, using the WITH clause.

• It is not stored in the database (unlike a table or view).
• It exists only for the duration of the query.
• It can make queries cleaner and easier to read, especially when dealing with complex joins, subqueries, or recursive queries.

Think of it as a named temporary query that you can reuse in the same SQL statement.

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
)
SELECT * FROM cte_name;

Advantages of CTEs

• Improves readability → Instead of deeply nested subqueries, break them into steps.
• Reusability within a query → A CTE can be referenced multiple times in the same statement.
• Recursive queries → Handle hierarchical data elegantly.
• Easier maintenance → Queries are modular and easier to debug.
• Acts like an inline view but more flexible.

Limitations of CTEs

• Performance: CTEs do not always optimize as well as derived tables.
  • In MySQL, non-recursive CTEs are often treated like inline views (they don’t persist results).
  • For very large datasets, temporary tables may perform better.
• Scope limited: A CTE exists only within the statement where it is defined.
• Not reusable across queries → Unlike a view, a CTE can’t be stored for future queries.
• MySQL Recursive CTEs have depth limits (default = 1000 levels).

Example of Simple CTE

Suppose we have an employees table:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT
);

Without CTE

If you want to find employees with salary above the average salary, you might write:

SELECT emp_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

With CTE

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT emp_id, name, salary
FROM employees, avg_salary
WHERE employees.salary > avg_salary.avg_sal;

Here avg_salary is a CTE that calculates the average salary and is reused in the main query.

This makes the query cleaner and more readable.

Example of Multiple CTEs

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
),
high_paid AS (
    SELECT emp_id, name, department, salary
    FROM employees
    WHERE salary > 70000
)
SELECT h.name, h.salary, d.avg_sal
FROM high_paid h
JOIN dept_avg d ON h.department = d.department;

• First CTE dept_avg calculates average salary per department.
• Second CTE high_paid selects high salary employees.
• Final query joins them.

Recursive CTEs

Recursive CTEs are used for hierarchical data, like org charts or parent-child relationships.

Suppose each employee has a manager_id (who is also an employee).

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: start with top-level manager (CEO, manager_id IS NULL)
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find employees reporting to the previous level
    SELECT e.emp_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM employee_hierarchy;

This will produce a tree-like structure of employees with their reporting levels.

CTE vs Subquery vs View

Feature	Subquery	CTE	View
Readability	❌ Harder	✅ Cleaner	✅ Good
Reusable in same query	❌ No	✅ Yes	✅ Yes
Reusable in different queries	❌ No	❌ No	✅ Yes
Stored permanently	❌ No	❌ No	✅ Yes
Recursive support	❌ No	✅ Yes	❌ No